using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class SPMFGRNDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procSPMFGRNPRV_add]";
        private string _strSPUpdateName = "dbo.[procSPMFGRNPRV_update]";
        private string _strSPDeleteName = "dbo.[procSPMFGRNPRV_delete]";
        private string _strSPGetName = "dbo.[procSPMFGRNPRV_get]";
        private string _strSPGetAllName = "dbo.[procSPMFGRNPRV_getall]";
        private string _strSPGetPages = "dbo.[procSPMFGRNPRV_getpaged]";
        private string _strSPIsExist = "dbo.[procSPMFGRNPRV_isexist]";
        private string _strTableName = "[SPMFGRNPRV]";
        private string _strSPGetTransferOutName = "dbo.[procSPMFGRNPRV_gettransferout]";
        string _strSPGetCountName = "procSPMFGRNPRV_getcount";
        string _strSPGetByIndexName = "procSPMFGRNPRV_getindex";
        #endregion Local Variable

        #region Method
        public SPMFGRNInfo Get(string db, String GRN_REF, ref string sErr)
        {
            string sql = @"SELECT        GRN_REF, GRN_DATE, STATUS, DEL_ADDR, ORDER_REF, TRANS_TYPE, ID_RELEASED, PAY_DATE, ORDER_DATE, Type, Period, TransDate, Reference, ShipmentDate, ShipmentBy, BLAWBNo, DeliveryPoint, 
                         Remark, From_, To_, Supplier, WellCode, Department, PINo, POSOContract, EX_SUPP_CODE1, EX_SUPP_CODE2, EX_TEXT1, EX_TEXT2, EX_TEXT3, EX_TEXT4, EX_TEXT5, EX_TEXT6, EX_TEXT7, EX_VAL1, 
                         EX_VAL2, EX_VAL3
            FROM            SPMFGRN" + db + @"
            WHERE        (GRN_REF = @GRN_REF)";
            SPMFGRNInfo objEntr = new SPMFGRNInfo();
            InitConnect();
            InitCommand(sql);
            AddParameter(SPMFGRNInfo.Field.GRN_REF.ToString(), GRN_REF);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            if (list.Rows.Count > 0)
                objEntr = new SPMFGRNInfo(list.Rows[0]);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        public DataTable GetAll(string db, ref string sErr)
        {
            string sql = @"SELECT        GRN_REF, GRN_DATE, STATUS, DEL_ADDR, ORDER_REF, TRANS_TYPE, ID_RELEASED, PAY_DATE, ORDER_DATE, Type, Period, TransDate, Reference, ShipmentDate, ShipmentBy, BLAWBNo, DeliveryPoint, 
                         Remark, From_, To_, Supplier, WellCode, Department, PINo, POSOContract, EX_SUPP_CODE1, EX_SUPP_CODE2, EX_TEXT1, EX_TEXT2, EX_TEXT3, EX_TEXT4, EX_TEXT5, EX_TEXT6, EX_TEXT7, EX_VAL1, 
                         EX_VAL2, EX_VAL3
            FROM            SPMFGRN" + db;
            InitConnect();
            InitCommand(sql);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(string db, SPMFGRNInfo objEntr, ref string sErr)
        {
            string sql = @"INSERT INTO SPMFGRN" + db + @"
                         (GRN_REF, GRN_DATE, STATUS, DEL_ADDR, ORDER_REF, TRANS_TYPE, ID_RELEASED, PAY_DATE, ORDER_DATE, Type, Period, TransDate, Reference, ShipmentDate, ShipmentBy, BLAWBNo, DeliveryPoint, 
                         Remark, From_, To_, Supplier, WellCode, Department, PINo, POSOContract, EX_SUPP_CODE1, EX_SUPP_CODE2, EX_TEXT1, EX_TEXT2, EX_TEXT3, EX_TEXT4, EX_TEXT5, EX_TEXT6, EX_TEXT7, EX_VAL1, 
                         EX_VAL2, EX_VAL3)
            VALUES        (@GRN_REF,@GRN_DATE,@STATUS,@DEL_ADDR,@ORDER_REF,@TRANS_TYPE,@ID_RELEASED,@PAY_DATE,@ORDER_DATE,@Type,@Period,@TransDate,@Reference,@ShipmentDate,@ShipmentBy,@BLAWBNo,@DeliveryPoint,@Remark,@From_,@To_,@Supplier,@WellCode,@Department,@PINo,@POSOContract,@EX_SUPP_CODE1,@EX_SUPP_CODE2,@EX_TEXT1,@EX_TEXT2,@EX_TEXT3,@EX_TEXT4,@EX_TEXT5,@EX_TEXT6,@EX_TEXT7,@EX_VAL1,@EX_VAL2,@EX_VAL3)";
            int ret = -1;
            openConnection();
            InitCommand(sql);
            AddParameter(SPMFGRNInfo.Field.GRN_REF.ToString(), objEntr.GRN_REF);
            AddParameter(SPMFGRNInfo.Field.GRN_DATE.ToString(), objEntr.GRN_DATE);
            AddParameter(SPMFGRNInfo.Field.STATUS.ToString(), objEntr.STATUS);
            AddParameter(SPMFGRNInfo.Field.DEL_ADDR.ToString(), objEntr.DEL_ADDR);
            AddParameter(SPMFGRNInfo.Field.ORDER_REF.ToString(), objEntr.ORDER_REF);
            AddParameter(SPMFGRNInfo.Field.TRANS_TYPE.ToString(), objEntr.TRANS_TYPE);
            AddParameter(SPMFGRNInfo.Field.ID_RELEASED.ToString(), objEntr.ID_RELEASED);
            AddParameter(SPMFGRNInfo.Field.PAY_DATE.ToString(), objEntr.PAY_DATE);
            AddParameter(SPMFGRNInfo.Field.ORDER_DATE.ToString(), objEntr.ORDER_DATE);
            AddParameter(SPMFGRNInfo.Field.Type.ToString(), objEntr.Type);
            AddParameter(SPMFGRNInfo.Field.Period.ToString(), objEntr.Period);
            AddParameter(SPMFGRNInfo.Field.TransDate.ToString(), objEntr.TransDate);
            AddParameter(SPMFGRNInfo.Field.Reference.ToString(), objEntr.Reference);
            AddParameter(SPMFGRNInfo.Field.ShipmentDate.ToString(), objEntr.ShipmentDate);
            AddParameter(SPMFGRNInfo.Field.ShipmentBy.ToString(), objEntr.ShipmentBy);
            AddParameter(SPMFGRNInfo.Field.BLAWBNo.ToString(), objEntr.BLAWBNo);
            AddParameter(SPMFGRNInfo.Field.DeliveryPoint.ToString(), objEntr.DeliveryPoint);
            AddParameter(SPMFGRNInfo.Field.Remark.ToString(), objEntr.Remark);
            AddParameter(SPMFGRNInfo.Field.From_.ToString(), objEntr.From_);
            AddParameter(SPMFGRNInfo.Field.To_.ToString(), objEntr.To_);
            AddParameter(SPMFGRNInfo.Field.Supplier.ToString(), objEntr.Supplier);
            AddParameter(SPMFGRNInfo.Field.WellCode.ToString(), objEntr.WellCode);
            AddParameter(SPMFGRNInfo.Field.Department.ToString(), objEntr.Department);
            AddParameter(SPMFGRNInfo.Field.PINo.ToString(), objEntr.PINo);
            AddParameter(SPMFGRNInfo.Field.POSOContract.ToString(), objEntr.POSOContract);
            AddParameter(SPMFGRNInfo.Field.EX_SUPP_CODE1.ToString(), objEntr.EX_SUPP_CODE1);
            AddParameter(SPMFGRNInfo.Field.EX_SUPP_CODE2.ToString(), objEntr.EX_SUPP_CODE2);
            AddParameter(SPMFGRNInfo.Field.EX_TEXT1.ToString(), objEntr.EX_TEXT1);
            AddParameter(SPMFGRNInfo.Field.EX_TEXT2.ToString(), objEntr.EX_TEXT2);
            AddParameter(SPMFGRNInfo.Field.EX_TEXT3.ToString(), objEntr.EX_TEXT3);
            AddParameter(SPMFGRNInfo.Field.EX_TEXT4.ToString(), objEntr.EX_TEXT4);
            AddParameter(SPMFGRNInfo.Field.EX_TEXT5.ToString(), objEntr.EX_TEXT5);
            AddParameter(SPMFGRNInfo.Field.EX_TEXT6.ToString(), objEntr.EX_TEXT6);
            AddParameter(SPMFGRNInfo.Field.EX_TEXT7.ToString(), objEntr.EX_TEXT7);
            AddParameter(SPMFGRNInfo.Field.EX_VAL1.ToString(), objEntr.EX_VAL1);
            AddParameter(SPMFGRNInfo.Field.EX_VAL2.ToString(), objEntr.EX_VAL2);
            AddParameter(SPMFGRNInfo.Field.EX_VAL3.ToString(), objEntr.EX_VAL3);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(string db, SPMFGRNInfo objEntr)
        {
            string sql = @"UPDATE       SPMFGRN" + db + @"
            SET                GRN_DATE = @GRN_DATE, STATUS = @STATUS, DEL_ADDR = @DEL_ADDR, ORDER_REF = @ORDER_REF, TRANS_TYPE = @TRANS_TYPE, ID_RELEASED = @ID_RELEASED, PAY_DATE = @PAY_DATE, 
                         ORDER_DATE = @ORDER_DATE, Type = @Type, Period = @Period, TransDate = @TransDate, Reference = @Reference, ShipmentDate = @ShipmentDate, ShipmentBy = @ShipmentBy, BLAWBNo = @BLAWBNo,
                          DeliveryPoint = @DeliveryPoint, Remark = @Remark, From_ = @From_, To_ = @To_, Supplier = @Supplier, WellCode = @WellCode, Department = @Department, PINo = @PINo, 
                         POSOContract = @POSOContract, EX_SUPP_CODE1 = @EX_SUPP_CODE1, EX_SUPP_CODE2 = @EX_SUPP_CODE2, EX_TEXT1 = @EX_TEXT1, EX_TEXT2 = @EX_TEXT2, EX_TEXT3 = @EX_TEXT3, 
                         EX_TEXT4 = @EX_TEXT4, EX_TEXT5 = @EX_TEXT5, EX_TEXT6 = @EX_TEXT6, EX_TEXT7 = @EX_TEXT7, EX_VAL1 = @EX_VAL1, EX_VAL2 = @EX_VAL2, EX_VAL3 = @EX_VAL3
            WHERE        (GRN_REF = @GRN_REF)";
            openConnection();
            InitCommand(sql);
            AddParameter(SPMFGRNInfo.Field.GRN_REF.ToString(), objEntr.GRN_REF);
            AddParameter(SPMFGRNInfo.Field.GRN_DATE.ToString(), objEntr.GRN_DATE);
            AddParameter(SPMFGRNInfo.Field.STATUS.ToString(), objEntr.STATUS);
            AddParameter(SPMFGRNInfo.Field.DEL_ADDR.ToString(), objEntr.DEL_ADDR);
            AddParameter(SPMFGRNInfo.Field.ORDER_REF.ToString(), objEntr.ORDER_REF);
            AddParameter(SPMFGRNInfo.Field.TRANS_TYPE.ToString(), objEntr.TRANS_TYPE);
            AddParameter(SPMFGRNInfo.Field.ID_RELEASED.ToString(), objEntr.ID_RELEASED);
            AddParameter(SPMFGRNInfo.Field.PAY_DATE.ToString(), objEntr.PAY_DATE);
            AddParameter(SPMFGRNInfo.Field.ORDER_DATE.ToString(), objEntr.ORDER_DATE);
            AddParameter(SPMFGRNInfo.Field.Type.ToString(), objEntr.Type);
            AddParameter(SPMFGRNInfo.Field.Period.ToString(), objEntr.Period);
            AddParameter(SPMFGRNInfo.Field.TransDate.ToString(), objEntr.TransDate);
            AddParameter(SPMFGRNInfo.Field.Reference.ToString(), objEntr.Reference);
            AddParameter(SPMFGRNInfo.Field.ShipmentDate.ToString(), objEntr.ShipmentDate);
            AddParameter(SPMFGRNInfo.Field.ShipmentBy.ToString(), objEntr.ShipmentBy);
            AddParameter(SPMFGRNInfo.Field.BLAWBNo.ToString(), objEntr.BLAWBNo);
            AddParameter(SPMFGRNInfo.Field.DeliveryPoint.ToString(), objEntr.DeliveryPoint);
            AddParameter(SPMFGRNInfo.Field.Remark.ToString(), objEntr.Remark);
            AddParameter(SPMFGRNInfo.Field.From_.ToString(), objEntr.From_);
            AddParameter(SPMFGRNInfo.Field.To_.ToString(), objEntr.To_);
            AddParameter(SPMFGRNInfo.Field.Supplier.ToString(), objEntr.Supplier);
            AddParameter(SPMFGRNInfo.Field.WellCode.ToString(), objEntr.WellCode);
            AddParameter(SPMFGRNInfo.Field.Department.ToString(), objEntr.Department);
            AddParameter(SPMFGRNInfo.Field.PINo.ToString(), objEntr.PINo);
            AddParameter(SPMFGRNInfo.Field.POSOContract.ToString(), objEntr.POSOContract);
            AddParameter(SPMFGRNInfo.Field.EX_SUPP_CODE1.ToString(), objEntr.EX_SUPP_CODE1);
            AddParameter(SPMFGRNInfo.Field.EX_SUPP_CODE2.ToString(), objEntr.EX_SUPP_CODE2);
            AddParameter(SPMFGRNInfo.Field.EX_TEXT1.ToString(), objEntr.EX_TEXT1);
            AddParameter(SPMFGRNInfo.Field.EX_TEXT2.ToString(), objEntr.EX_TEXT2);
            AddParameter(SPMFGRNInfo.Field.EX_TEXT3.ToString(), objEntr.EX_TEXT3);
            AddParameter(SPMFGRNInfo.Field.EX_TEXT4.ToString(), objEntr.EX_TEXT4);
            AddParameter(SPMFGRNInfo.Field.EX_TEXT5.ToString(), objEntr.EX_TEXT5);
            AddParameter(SPMFGRNInfo.Field.EX_TEXT6.ToString(), objEntr.EX_TEXT6);
            AddParameter(SPMFGRNInfo.Field.EX_TEXT7.ToString(), objEntr.EX_TEXT7);
            AddParameter(SPMFGRNInfo.Field.EX_VAL1.ToString(), objEntr.EX_VAL1);
            AddParameter(SPMFGRNInfo.Field.EX_VAL2.ToString(), objEntr.EX_VAL2);
            AddParameter(SPMFGRNInfo.Field.EX_VAL3.ToString(), objEntr.EX_VAL3);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(string db, String GRN_REF)
        {
            string sql = @"DELETE FROM SPMFGRN" + db + @"
            WHERE        (GRN_REF = @GRN_REF)";
            openConnection();
            InitCommand(sql);
            AddParameter(SPMFGRNInfo.Field.GRN_REF.ToString(), GRN_REF);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }


        public Boolean IsExist(string db, String GRN_REF)
        {
            string sql = @"SELECT        TOP (1) GRN_REF
            FROM            SPMFGRN" + db + @"
            WHERE        (GRN_REF = @GRN_REF)";
            InitConnect();
            InitCommand(sql);
            AddParameter(SPMFGRNInfo.Field.GRN_REF.ToString(), GRN_REF);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(SPMFGRNInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, string tableName, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            InitConnect();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }


        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetTransferOutName);
            AddParameter("DB", dtb);
            AddParameter("FROM", from);
            AddParameter("TO", to);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method



        public string ExecuteQuery(string db, string GRNref, ref string sErr)
        {
            string sql = @"update SPMFGRN" + db + " set STATUS = 'Y' where GRN_REF = @GRNref ";
            openConnection();
            InitCommand(sql, CommandType.Text);
            AddParameter("GRNref", GRNref);

            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }

            return sErr;
        }

    }
}
